﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace Financeiro.Dados
{
    public class LoginDAO
    {
        private static string connectionSettings = ConfigurationManager.ConnectionStrings["ERP_FinanceiroConnectionString"].ConnectionString;


        public static bool autenticar(string userName, string passWord)
        {
            SqlCommand cmd;
            string lookupPassword = null;

            // Check for invalid userName. 	
            // userName must not be null and must be between 1 and 15 characters. 	
            if ((null == userName) || (0 == userName.Length) || (userName.Length > 15))
            {
                return false;
            }

            // Check for invalid passWord. 	
            // passWord must not be null and must be between 1 and 25 characters. 	
            if ((null == passWord) || (0 == passWord.Length) || (passWord.Length > 25))
            {
                return false;
            }

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                SqlConnection Conn = new SqlConnection(connectionSettings);
                Conn.Open();
                // Create SqlCommand to select pwd field from users table given supplied userName.
                cmd = new SqlCommand("Select senha from Login where login=@userName", Conn);
                cmd.Parameters.Add("@userName", SqlDbType.VarChar, 25);
                cmd.Parameters["@userName"].Value = userName;
                // Execute command and fetch pwd field into lookupPassword string.
                lookupPassword = (string)cmd.ExecuteScalar();
                // Cleanup command and connection objects.
                cmd.Dispose();
                Conn.Dispose();
            }
            catch
            {
            }

            // If no password found, return false. 
            if (null == lookupPassword)
            {
                return false;
            }
            // Compare lookupPassword and input passWord, using a case-sensitive comparison. 	

            // aqui transformamos a password com a mesma funcao de hash para comparar com o banco
            
            HashAlgorithm mhash = new SHA1CryptoServiceProvider();
            byte[] bytValue = System.Text.Encoding.UTF8.GetBytes(passWord);
            byte[] bytHash = mhash.ComputeHash(bytValue);
            mhash.Clear();
            passWord = Convert.ToBase64String(bytHash);

            return (0 == string.Compare(lookupPassword, passWord, false));
        }

        public static bool registar(string login, string senha, string nome)
        {
            try
            {
                SqlConnection Conn = new SqlConnection(connectionSettings);
                SqlCommand cmd = new SqlCommand(@"INSERT INTO login (login, senha, dataCriacao, nome) VALUES (@login, @senha, GETDATE(), @nome)", Conn);

                cmd.Parameters.Add("@login", SqlDbType.VarChar);
                cmd.Parameters["@login"].Value = login;

                cmd.Parameters.Add("@senha", SqlDbType.VarChar);
                cmd.Parameters["@senha"].Value = senha;

                cmd.Parameters.Add("@nome", SqlDbType.VarChar);
                cmd.Parameters["@nome"].Value = nome;

                Conn.Open();
                cmd.ExecuteNonQuery();
                Conn.Dispose();

                return true;

            }
            catch
            {
                return false;
            }
        }


        public static DataTable buscarLogins()
        {
            string sql = "SELECT id, nome, login FROM login";
            try
            {
                SqlConnection Conn = new SqlConnection(connectionSettings);
                Conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, Conn);

                DataSet ds = new DataSet();
                da.Fill(ds, "Logins");
                Conn.Close();

                DataTable dt = ds.Tables[0];

                return dt;

            }
            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro no método buscarLogins: " + ex.Message);
            }
        }

        public static void apagaLogin(int id)
        {
            try
            {
                SqlConnection Conn = new SqlConnection(connectionSettings);
                SqlCommand cmd = new SqlCommand(@"DELETE FROM Login WHERE id = @id", Conn);

                cmd.Parameters.Add("@id", SqlDbType.Int);
                cmd.Parameters["@id"].Value = id;

                Conn.Open();
                cmd.ExecuteNonQuery();
                Conn.Dispose();

            }
            catch (Exception ex)
            {
              throw new Exception("Ocorreu um erro no método apagaLogin: " + ex.Message);
            
            }
        }


        public static int buscarId(string login)
        {
            try
            {
                int id = 0;
                SqlCommand cmd;
                SqlConnection Conn = new SqlConnection(connectionSettings);
                Conn.Open();
                cmd = new SqlCommand("SELECT id FROM login WHERE login= @login", Conn);
                cmd.Parameters.Add("@login", SqlDbType.VarChar);
                cmd.Parameters["@login"].Value = login;

                if (!(cmd.ExecuteScalar() is DBNull))
                    id = (int)cmd.ExecuteScalar();

                return id;
            }
            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro no método buscarId: " + ex.Message);
            }
        }
        

    }
}
